package json;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Set;

import static json.ParseJsonUtil.readFileContent;

/**
 * JSON转EXCEL，支持多个数组生成多个sheet
 * https://blog.csdn.net/hanne_lovegood/article/details/124351031
 */
public class JsonToExcel {

    public static void main(String[] args) throws Exception {

        // json文件地址
        String jsonFilePath = "D:\\export_tmp\\test4.txt";
        // 设置生成的文件名及路径
        String fileName = "D:\\export_tmp\\test4.xlsx";

        // 保证文件存在
        File file = new File(jsonFilePath);
        if (!file.exists()) {
            System.out.println("json文件不存在");
        }
        String jsonString = readFileContent(file);
        if (jsonString == null || jsonString.trim().length() == 0) {
            System.out.println("文件内容为null");
        }

        JSONObject jsonObject = null;
        // JSONObject jsonObject = JSONObject.parseObject(jsonString, Feature.OrderedField);
        // java判断JSON字符串是JSONObject或JSONArray https://blog.csdn.net/weixin_45353083/article/details/109749473
        // Feature.OrderedField fastJson 中提供的 将字符串转为某些对象时，为了保证对象内部的顺序保持不变
        Object object = JSONObject.parse(jsonString, Feature.OrderedField);
        if (object instanceof JSONObject) {
            jsonObject = (JSONObject) object;
        } else if (object instanceof JSONArray) {
            jsonObject = new JSONObject();
            JSONArray jsonArray = (JSONArray) object;
            jsonObject.put("sheet", jsonArray);
        } else{
            System.out.println("文件格式错误");
            return;
        }

        // 调用转换方法
        jsonToExcel(fileName, jsonObject);

        System.out.println("生成文件成功：" + fileName);
    }

    public static void jsonToExcel(String fileName, JSONObject jsonObject) throws Exception {
        // 创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();

        Set<String> ArrayKeys = jsonObject.keySet();
        // 多个数组的建成多个sheet
        for (String arrayKey : ArrayKeys) {
            Object value = jsonObject.get(arrayKey);
            if (!(value instanceof JSONArray)) {
                // 非数组则跳过，只处理第一层，嵌套json暂不考虑
                continue;
            }
            JSONArray jsonArray = jsonObject.getJSONArray(arrayKey);
            if (jsonArray == null || jsonArray.isEmpty()) {
                continue;
            }

            // 创建HSSFSheet对象
            HSSFSheet sheet = wb.createSheet(ArrayKeys.size() > 1 ? arrayKey : "sheet");
            Set keys = null;
            int rowNo = 0;
            HSSFRow row = sheet.createRow(0);

            // 获取标题
            for (int i = 0; i < jsonArray.size(); i++) {
                if (keys == null) {
                    JSONObject item = jsonArray.getJSONObject(i);
                    keys = item.keySet();
                    for (Object s : keys) {
                        HSSFCell cell = row.createCell(rowNo++);
                        cell.setCellValue(s.toString());
                    }
                } else {
                    break;
                }
            }
            // 获取数据一次循环一行
            for (int i = 0; i < jsonArray.size(); i++) {
                rowNo = 0;
                JSONObject item = jsonArray.getJSONObject(i);
                row = sheet.createRow(i + 1);
                keys = item.keySet();
                for (Object s : keys) {
                    HSSFCell cell = row.createCell(rowNo++);
                    cell.setCellValue(item.getString(s.toString()));
                }
            }
        }
        // 创建Excel文件
        File file = new File(fileName);
        file.createNewFile();
        // 输出到Excel文件
        FileOutputStream output = new FileOutputStream(fileName);
        wb.write(output);

        wb.close();
        output.flush();
        output.close();
    }

}
